/*
This view is used from vContactCorrespondencePreferencesReport in order to
provide preference information for contact's address category.
The resultset includes contact and specific preference information for
mailing, phone, fax and email communication methods.
*/
CREATE VIEW [dbo].[vContactAddressCategoryPreferencesReport]
AS
SELECT  cm.ContactKey,
        mail.NoSolicitationFlag AS MailNoSolicitationFlag,
        mail.OptOutFlag AS MailOptOutFlag,
        phone.NoSolicitationFlag AS PhoneNoSolicitationFlag,
        phone.OptOutFlag AS PhoneOptOutFlag,
        fax.NoSolicitationFlag AS FaxNoSolicitationFlag,
        fax.OptOutFlag AS FaxOptOutFlag,
        email.NoSolicitationFlag AS EmailNoSolicitationFlag,
        email.OptOutFlag AS EmailOptOutFlag
  FROM ContactMain cm LEFT OUTER JOIN AddressCategoryPreferences mail
       ON cm.ContactKey = mail.ContactKey
       AND mail.AddressCategoryCode = 1
       LEFT OUTER JOIN AddressCategoryPreferences phone
       ON cm.ContactKey = phone.ContactKey
       AND phone.AddressCategoryCode = 2
       LEFT OUTER JOIN AddressCategoryPreferences fax
       ON cm.ContactKey = fax.ContactKey
       AND fax.AddressCategoryCode = 3
       LEFT OUTER JOIN AddressCategoryPreferences email
       ON cm.ContactKey = email.ContactKey
       AND email.AddressCategoryCode = 4

GO